from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, INT, Boolean, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import event
import time
import config


def get_session(db_url):
    engine = create_engine(db_url,
                           pool_size=20,
                           pool_recycle=3600,
                           pool_timeout=3600)
    metadata = MetaData()
    return (engine, scoped_session(sessionmaker(autocommit=False,
                                                autoflush=False,
                                                bind=engine)))

#scoped_session is thread-local
(engine, db_session) = get_session(config.DATABASEURI)
Base = declarative_base()


class InstallUserMap(Base):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
    __tablename__ = 'InstallUserMap'
    uname = Column(String(200), primary_key=True)
    uid = Column(INT, primary_key=True)
    mtime = Column(INT, nullable=True)


class Agents(Base):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
    __tablename__ = 'Agents'
    ip = Column(String(15), primary_key=True)
    passwd = Column(String(200), nullable=True)
    port = Column(INT, nullable=True)
    uptime = Column(INT, nullable=True)
    lastcontact = Column(INT, nullable=True)


class Ipgroup(Base):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
    __tablename__ = 'Ipgroup'
    ip = Column(String(15), primary_key=True)
    groupname = Column(String(200), primary_key=True)
    isvalid = Column(Boolean, nullable=True)
    mtime = Column(INT, nullable=True)


class Command(Base):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
    __tablename__ = 'Command'
    clientuid = Column(String(100), primary_key=True)
    ip = Column(String(15), primary_key=True)
    name = Column(String(100), primary_key=True)
    signature = Column(String(100), nullable=True)
    content = Column(String(5000), nullable=True)
    result = Column(String(2000), nullable=True)
    error = Column(String(2000), nullable=True)
    createtime = Column(INT, nullable=True)
    fetchtime = Column(INT, nullable=True)
    starttime = Column(INT, nullable=True)
    finishtime = Column(INT, nullable=True)


class Item(Base):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
    __tablename__ = 'Item'
    uid = Column(Integer, primary_key=True, autoincrement=True)
    ip = Column(String(15))
    path = Column(String(100))
    filename = Column(String(100))
    md5 = Column(String(200), nullable=True)
    config = relationship("ItemConfigs")


class ItemConfigs(Base):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
    __tablename__ = 'ItemConfigs'
    uid = Column(Integer, ForeignKey('Item.uid'), primary_key=True)
    propertyname = Column(String(100), primary_key=True)
    value = Column(String(600))
    mtime = Column(INT)


@event.listens_for(Ipgroup, "before_insert")
def gen_default_igroup_before_insert(mapper, connection, instance):
    instance.mtime = int(time.time())
    instance.isvalid = True


@event.listens_for(InstallUserMap, "before_insert")
def gen_default_usermap_before_insert(mapper, connection, instance):
    instance.mtime = int(time.time())


@event.listens_for(InstallUserMap, "before_update")
def gen_default_usermap_before_update(mapper, connection, instance):
    if instance.result or instance.error:
        instance.mtime = int(time.time())


@event.listens_for(Command, "before_insert")
def gen_default_command_before_insert(mapper, connection, instance):
    instance.createtime = int(time.time())


@event.listens_for(Command, "before_update")
def gen_default_command_before_update(mapper, connection, instance):
    if instance.result or instance.error:
        instance.finishtime = int(time.time())


@event.listens_for(Agents, "before_insert")
def gen_default_agents_before_insert(mapper, connection, instance):
    instance.uptime = int(time.time())


@event.listens_for(ItemConfigs, "before_insert")
def gen_default_itemconfigs_before_insert(mapper, connection, instance):
    instance.mtime = int(time.time())


Base.metadata.create_all(engine)
